#include "MysqlUtils.h"

#include <ChatServer.h>
#include "app/config/Config.h"

#include <array>
#include <boost/asio/detail/chrono.hpp>
#include <boost/asio/ip/tcp.hpp>
#include <boost/asio/steady_timer.hpp>
#include <boost/bind.hpp>
#include <boost/core/ref.hpp>
#include <boost/date_time/gregorian/formatters.hpp>
#include <boost/date_time/gregorian/gregorian.hpp>
#include <boost/date_time/posix_time/posix_time.hpp>
#include <boost/date_time/posix_time/time_formatters.hpp>
#include <boost/exception/exception.hpp>
#include <boost/mysql.hpp>
#include <boost/mysql/error_code.hpp>
#include <boost/mysql/error_with_diagnostics.hpp>
#include <boost/mysql/tcp_ssl.hpp>
#include <boost/smart_ptr/make_shared_object.hpp>
#include <boost/system/detail/error_code.hpp>
#include <boost/system/system_error.hpp>
#include <exception>
#include <iostream>
#include <ostream>
#include <string>
#include <system/System.h>
#include <tuple>

std::string to_string(boost::mysql::datetime datetime) {
    // 构造 ptime 对象
    boost::posix_time::ptime pt(
        boost::gregorian::date(datetime.year(), datetime.month(), datetime.day()),
        boost::posix_time::time_duration(datetime.hour(), datetime.minute(), datetime.second()));

    // 使用 to_simple_string 格式化
    return boost::posix_time::to_simple_string(pt);
}

std::string to_string(boost::mysql::date date) {
    // 将 boost::mysql::date 转换为 boost::gregorian::date
    boost::gregorian::date gregorianDate = boost::gregorian::date(
        date.year(), date.month(), date.day());
    // 使用 boost::gregorian::to_iso_extended_string 将日期转换为 yyyy-mm-dd
    return boost::gregorian::to_iso_extended_string(gregorianDate);
}

bool MysqlUtils::stop = false;
boost::asio::ssl::context MysqlUtils::ssl_ctx(boost::asio::ssl::context::tls_client);
boost::mysql::tcp_ssl_connection* MysqlUtils::mysqlconn = nullptr;

void MysqlUtils::ping_loop(boost::asio::io_context& ctx, boost::shared_ptr<boost::asio::steady_timer> timer) {
    static std::array<std::string, 3> strs = {".", "..", "..."};
    static int num                         = 0;
    try {
        // 检查与数据库的连通性
        mysqlconn->ping();
        std::clog << "mysqlconn->ping() " << strs[(num++) % 3] << std::endl;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        auto code = e.code();
        auto dia  = e.get_diagnostics();
        std::cerr << __func__ << e.what() << "code:" << code << std::endl;
        switch (code.value()) {
            case static_cast<int>(boost::mysql::common_server_errc::er_signal_exception):

                break;
            case static_cast<int>(10054):
            case 10009:
            case 10060:
            case 10065:

                std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;

            default:
                break;
        }
        // auto code = e.code();
        // std::cerr << __func__ << e.what() << "code:" << code << std::endl;
        // std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;
    }
    catch (std::exception& e) {
        std::cerr << __func__ << e.what() << std::endl;
        std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;
    }

    // 如果没有停止，继续启动定时器
    if (!stop) {
        timer->expires_at(timer->expiry() + boost::asio::chrono::seconds(60));
        timer->async_wait([&ctx, timer](boost::system::error_code e) {
            MysqlUtils::ping_loop(ctx, timer);
        });
    }
}

void MysqlUtils::do_exception(boost::mysql::error_with_diagnostics& e, std::string func) {
    auto code = e.code();
    auto dia  = e.get_diagnostics();
    std::cerr << __func__ << e.what() << " server message: " << dia.server_message() << std::endl;
    switch (code.value()) {
        case static_cast<int>(boost::mysql::common_server_errc::er_signal_exception):

            break;
        case 10054:  // 远程主机强迫关闭了一个现有的连接。
        case 10009:  // 提供的文件句柄无效。
        case 10060:  // 由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败。
        case 10065:  // 套接字操作尝试一个无法连接的主机。
            std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;

        default:
            break;
    }
}

void MysqlUtils::do_exception(const std::exception& e, std::string func) {
    last_error = e.what();
    std::cerr << "MysqlUtils::" << func << "() " << e.what() << std::endl;
}

void MysqlUtils::start_ping(boost::asio::io_context& ioc) {
    // 创建定时器
    auto timer = boost::make_shared<boost::asio::steady_timer>(
        ioc.get_executor(), boost::asio::chrono::seconds(60));

    // 启动定时器
    timer->async_wait([&ioc, timer](boost::system::error_code e) {
        MysqlUtils::ping_loop(ioc, timer);
    });
}

bool MysqlUtils::connect() {
    bool ret = reconnect();
    start_ping(ChatServer::io_context());
    return ret;
}

bool MysqlUtils::reconnect() {
    Config config;
    config.loadConfig("./config/config.json");
    boost::asio::io_context& ctx = ChatServer::io_context();

    if (mysqlconn != nullptr) {
        delete mysqlconn;
    }

    mysqlconn = new boost::mysql::tcp_ssl_connection(ctx, ssl_ctx);
    // Resolve the hostname to get a collection of endpoints
    boost::asio::ip::tcp::resolver resolver(ctx.get_executor());

    auto endpoints = resolver.resolve(config.mysql.hostname, boost::mysql::default_port_string);

    // The username, password and database to use
    boost::mysql::handshake_params params(
        config.mysql.username,  // username
        config.mysql.password,  // password
        config.mysql.database   // database
    );
    // Connect to the server using the first endpoint returned by the resolver
    try {
        mysqlconn->connect(*endpoints.begin(), params);

        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        auto code = e.code();
        auto dia  = e.get_diagnostics();

        std::cerr << "MysqlUtils::" << __func__ << "() " << e.what() << " server_message:" << dia.server_message() << std::endl;
    }
    catch (const std::exception& e) {
        std::cerr << e.what() << '\n';
    }

    return false;
}

std::string MysqlUtils::lastError() { return last_error; }

/**
 * @brief 查询用户账户
 *
 * @param account 调用时可选填 account.userId 或 account.account，返回账号密码
 * @return true 用户存在
 * @return false 用户不存在
 */
bool MysqlUtils::getAcount(data::Account& account) {
    try {
        boost::mysql::statement stmt =
            // mysqlconn->prepare_statement("SELECT userId, account, password FROM
            // `account` where userId = ? or account = ?");
            mysqlconn->prepare_statement(
                "CALL Login(?,?, @out_userId, @out_account, @out_password)");

        boost::mysql::results r;
        mysqlconn->execute_statement(
            stmt, std::make_tuple(account.userid(), account.account()), r);

        boost::mysql::results result;
        mysqlconn->query("select @out_userId, @out_account, @out_password", result);

        // std::cout<< "meta:" << result.meta() << std::endl;
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            auto row = result.rows()[0];
            // account  = {row[0].get_string(), row[1].get_string(), row[2].get_string()};
            account.set_userid(row[0].get_string());
            account.set_account(row[1].get_string());
            account.set_password(row[2].get_string());
            return true;
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

/**
 * @brief 获取用户详细信息
 *
 * @param userId 用户名
 * @param userInfo 返回用户详细信息
 * @return true
 * @return false
 */
bool MysqlUtils::getUserInfo(std::string userId, data::UserInfo& userInfo) {
    try {
        boost::mysql::statement stmt = mysqlconn->prepare_statement(
            R"(SELECT userId, account, name, sign, age, sex, address,phone, email, headshot, birthday  FROM `userinfo`WHERE userId = ?)");
        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(userId), result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            auto row = result.rows()[0];

            userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
            userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
            userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
            userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
            userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
            userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
            userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
            userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
            userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
            userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());

            boost::mysql::date mysqlDate =
                row[10].is_null() ? boost::mysql::date::now() : row[10].get_date();

            userInfo.set_birthday(to_string(mysqlDate));

            return true;
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

/**
 * @brief 设置用户详细信息
 *
 * @param user
 * @return true
 * @return false
 */
bool MysqlUtils::setUserInfo(const data::UserInfo& user) {
    try {
        static auto stmt = mysqlconn->prepare_statement(R"(
UPDATE `boost_chat`.`userinfo`
SET `name` = ?,`sign` = ?,`age` = ?,`sex` = ?,`address` = ?,`phone` = ?,`email` = ?,`headshot` = ?,`birthday` = ?
WHERE `userId` = ?
    )");

        boost::mysql::results result;
        mysqlconn->execute_statement(
            stmt,
            std::make_tuple(user.name(), user.sign(), user.age(), user.sex(), user.address(),
                user.phonenumber(), user.email(), user.headshot(),
                user.birthday(), user.userid()),
            result);

        if (!result.rows().empty()) {
            auto row = result.rows()[0];
            for (auto&& i : row) {
                std::cout << i.get_string();
            }
            std::cout << std::endl;
        }
        else {
            std::cout << result.warning_count() << std::endl;
        }

        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

bool MysqlUtils::setAcount(const data::Account& account) {
    try {
        static auto stmt = mysqlconn->prepare_statement("CALL SetAccount(?,?,?)");

        boost::mysql::results result;
        mysqlconn->execute_statement(
            stmt,
            std::make_tuple(account.userid(), account.account(), account.password()),
            result);

        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

int MysqlUtils::getFriends(std::string myuserId,
    std::vector<data::FriendInfo>& users) {
    data::FriendInfo userInfo = {};
    try {
        boost::mysql::statement stmt = mysqlconn->prepare_statement(R"(
SELECT a.friendUserId, b.account, b.name, b.sign, b.age, b.sex, b.address, b.phone, b.email, b.headshot, a.group, a.acquaintanceTime
FROM friends AS a
JOIN userinfo AS b ON a.friendUserId = b.userId
WHERE a.myUserId = ? and a.status = 1
        )");

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(myuserId), result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
                userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
                userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
                userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
                userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
                userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());
                userInfo.set_group(row[10].is_null() ? "" : row[10].get_string());
                userInfo.set_acquaintancetime(row[11].is_null() ? "" : to_string(row[11].get_datetime()));

                users.push_back(userInfo);
            }
            return users.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return users.size();
}

bool MysqlUtils::getFriendInfo(std::string myuserId, std::string friendsId, data::FriendInfo& userInfo) {
    try {
        boost::mysql::statement stmt = mysqlconn->prepare_statement(R"(
SELECT a.friendUserId, b.account, b.name, b.sign, b.age, b.sex, b.address, b.phone, b.email, b.headshot, a.group, a.acquaintanceTime
FROM friends AS a
JOIN userinfo AS b ON a.friendUserId = b.userId
WHERE a.myUserId = ? AND a.friendUserId = ?
        )");

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(myuserId, friendsId), result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
                userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
                userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
                userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
                userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
                userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());
                userInfo.set_group(row[10].is_null() ? "" : row[10].get_string());
                userInfo.set_acquaintancetime(row[11].is_null() ? "" : to_string(row[11].get_datetime()));
            }
            return true;
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

bool MysqlUtils::createRoom(data::ChatRoomInfo& info) {
    try {
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(R"(CALL CreateRoom(?,?,?,?, @room_id))");

        boost::mysql::results r;
        mysqlconn->execute_statement(
            stmt,
            std::make_tuple(info.userid(), info.name(), info.sign(), info.headshot()),
            r);

        boost::mysql::results result;
        mysqlconn->query("select @room_id", result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            auto row = result.rows()[0];
            info.set_roomid(row[0].get_string());
            return true;
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

bool MysqlUtils::addRoomMember(std::string roomid, std::string userid) {
    try {
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(R"(CALL addRoomMember(?,?))");

        boost::mysql::results r;
        mysqlconn->execute_statement(
            stmt,
            std::make_tuple(roomid, userid),
            r);

        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}
bool MysqlUtils::delRoomMember(std::string roomid, std::string userid) {
    try {
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(R"(CALL DelRoomMember(?,?))");

        boost::mysql::results r;
        mysqlconn->execute_statement(
            stmt,
            std::make_tuple(roomid, userid),
            r);

        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

int MysqlUtils::getRoomsMembers(std::string roomId,
    std::vector<data::UserInfo>& users) {
    if (roomId == "10001") {
        return getAllUsers(users);
    }

    data::UserInfo userInfo = {};
    try {
        boost::mysql::statement stmt = mysqlconn->prepare_statement(R"(
SELECT a.userId, b.account, b.name, b.sign, b.age, b.sex, b.address, b.phone, b.email, b.headshot
FROM group_members AS a
JOIN userinfo AS b ON a.userId = b.userId
WHERE a.groupId = ?
        )");

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(roomId), result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
                userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
                userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
                userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
                userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
                userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());
                users.push_back(userInfo);
            }
            return users.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return 0;
}

int MysqlUtils::getAllUsers(std::vector<data::UserInfo>& users) {
    data::UserInfo userInfo = {};
    try {
        boost::mysql::results result;
        mysqlconn->query("SELECT userId, account, name, sign, age, sex, "
                         "address,phone, email, headshot FROM `userinfo`",
            result);

        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
                userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
                userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
                userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
                userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
                userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());
                users.push_back(userInfo);
            }
            return users.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return 0;
}

int MysqlUtils::getRooms(std::string myuserId,
    std::vector<data::ChatRoomInfo>& rooms) {
    data::ChatRoomInfo userInfo = {};
    try {
        boost::mysql::statement stmt = mysqlconn->prepare_statement(R"(
SELECT a.groupId, a.userId, a.name, a.sign, a.headshot 
FROM groupinfo AS a
LEFT JOIN group_members AS b ON a.groupId = b.groupId
WHERE a.groupId = 10001 OR b.userId = ?
        )");

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(myuserId), result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_roomid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_userid(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_headshot(row[4].is_null() ? "" : row[4].get_string());
                rooms.push_back(userInfo);
            }
            return rooms.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
    return 0;
}

int MysqlUtils::getRooms(std::vector<data::ChatRoomInfo>& rooms) {
    data::ChatRoomInfo userInfo = {};
    try {
        static std::string sql = R"(
SELECT groupId, userId, name, sign, headshot 
FROM groupinfo
        )";

        boost::mysql::results result;
        mysqlconn->query(sql, result);

        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_roomid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_userid(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_headshot(row[4].is_null() ? "" : row[4].get_string());
                rooms.push_back(userInfo);
            }
            return rooms.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
    return 0;
}

bool MysqlUtils::registerUser(data::UserInfo& user, data::Account& account) {
    try {
        boost::mysql::results r;
        boost::mysql::results result;
        boost::mysql::statement stmt_register =
            mysqlconn->prepare_statement(R"(CALL RegisterUser(?, ?,
            @out_userId,@out_account,@out_name,@out_sign,@out_age,@out_sex,@out_address,@out_phone,@out_email,@out_headshot,@out_birthday
        ))");

        mysqlconn->execute_statement(
            stmt_register,
            std::make_tuple(account.account(), account.password()), r);

        mysqlconn->query("SELECT @out_userId,@out_account,@out_name,@out_sign,@out_age,@out_sex,@out_address,@out_phone,@out_email,@out_headshot,@out_birthday", result);
        auto row = result.rows()[0];

        user.set_userid(row[0].is_null() ? "" : row[0].get_string());
        user.set_account(row[1].is_null() ? "" : row[1].get_string());
        user.set_name(row[2].is_null() ? "" : row[2].get_string());
        user.set_sign(row[3].is_null() ? "" : row[3].get_string());
        user.set_age(row[4].is_null() ? 0 : row[4].get_int64());
        user.set_sex(row[5].is_null() ? "0" : row[5].get_string());
        user.set_address(row[6].is_null() ? "" : row[6].get_string());
        user.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
        user.set_email(row[8].is_null() ? "" : row[8].get_string());
        user.set_headshot(row[9].is_null() ? "" : row[9].get_string());

        account.set_userid(user.userid());
        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}

int MysqlUtils::searchAutor(std::string text, std::vector<data::UserInfo>& users) {
    // SELECT * FROM `userinfo` WHERE account LIKE "hans" or userId LIKE "han" or `name` LIKE "%斯%";
    data::UserInfo userInfo = {};
    try {
        static std::string sql = R"(
SELECT userId, account, name, sign, age, sex, address, phone, email, headshot FROM `userinfo` 
WHERE account LIKE ? or userId LIKE ? or `name` LIKE ?;
        )";

        text.insert(text.begin(), '%');
        text.push_back('%');
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(sql);

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(text, text, text), result);

        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
                userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
                userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
                userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
                userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
                userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());
                users.push_back(userInfo);
            }
            return users.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
    return users.size();
}

int MysqlUtils::searchGroup(std::string text, std::vector<data::ChatRoomInfo>& groups) {
    data::ChatRoomInfo info = {};
    try {
        static std::string sql = R"(
SELECT groupId, userId, name, sign, headshot 
FROM groupinfo
WHERE groupId LIKE ? OR `name` LIKE ? ;
        )";

        text.insert(text.begin(), '%');
        text.push_back('%');
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(sql);

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(text, text), result);

        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                info.set_roomid(row[0].is_null() ? "" : row[0].get_string());
                info.set_userid(row[1].is_null() ? "" : row[1].get_string());
                info.set_name(row[2].is_null() ? "" : row[2].get_string());
                info.set_sign(row[3].is_null() ? "" : row[3].get_string());
                info.set_headshot(row[4].is_null() ? "" : row[4].get_string());
                groups.push_back(info);
            }
            return groups.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
    return groups.size();
}

void MysqlUtils::addFriends(std::string userid, std::string friendsId) {
    try {
        boost::mysql::results r;
        boost::mysql::statement stmt_register =
            mysqlconn->prepare_statement("CALL AddFriends(?, ?)");

        mysqlconn->execute_statement(
            stmt_register,
            std::make_tuple(userid, friendsId), r);
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
}

void MysqlUtils::delFriends(std::string userid, std::string friendsId) {
    try {
        boost::mysql::results r;
        boost::mysql::statement stmt_register =
            mysqlconn->prepare_statement("CALL DelFriends(?, ?)");

        mysqlconn->execute_statement(
            stmt_register,
            std::make_tuple(userid, friendsId), r);
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
}
